﻿CREATE TABLE [dbo].[ApptOrder] (
    [Id]                    INT             IDENTITY (1, 1) NOT NULL,
    [OrderType]             NVARCHAR (50)   NULL,
    [OpenId]                NVARCHAR (50)   NOT NULL,
    [CardType]              NVARCHAR (50)   NULL,
    [CardNo]                NVARCHAR (50)   NULL,
    [HospitalId]            NVARCHAR (50)   NULL,
    [BranchNo]              NVARCHAR (50)   NULL,
    [DeptId]                NVARCHAR (50)   NULL,
    [DeptName]              NVARCHAR (50)   NULL,
    [DoctorId]              NVARCHAR (50)   NULL,
    [DoctorName]            NVARCHAR (50)   NULL,
    [OrderNo]               NVARCHAR (50)   NOT NULL,
    [RegDate]               NVARCHAR (50)   NULL,
    [RegNo]                 NVARCHAR (50)   NULL,
    [SuggestBeginTime]      NVARCHAR (50)   NULL,
    [SuggestEndTime]        NVARCHAR (50)   NULL,
    [Location]              NVARCHAR (200)  NULL,
    [OrderStatus]           INT             NOT NULL,
    [TimeSlice]             NVARCHAR (50)   NULL,
    [StartTime]             NVARCHAR (50)   NULL,
    [EndTime]               NVARCHAR (50)   NULL,
    [RegType]               NVARCHAR (50)   NULL,
    [PayState]              NVARCHAR (50)   NULL,
    [RefundReason]          NVARCHAR (50)   NULL,
    [HaltState]             NVARCHAR (50)   DEFAULT ((0)) NULL,
    [Fee]                   DECIMAL (18, 2) NULL,
    [TreatFee]              DECIMAL (18, 2) NULL,
    [OtherFee]              DECIMAL (18, 2) NULL,
    [SumFee]                DECIMAL (18, 2) NULL,
    [CreateTime]            DATETIME        NULL,
    [LockTime]              DATETIME        NULL,
    [ApptTime]              DATETIME        NULL,
    [PayTime]               DATETIME        NULL,
    [FetchTime]             DATETIME        NULL,
    [RefundTime]            DATETIME        NULL,
    [CancelByUserTime]      DATETIME        NULL,
    [CancelByHospTime]      DATETIME        NULL,
    [CancelBySysTime]       DATETIME        NULL,
    [NextCheckTime]         DATETIME        NULL,
    [OrderStatusOnSetCheck] INT             NULL,
    [CheckReserved]         NVARCHAR (50)   NULL,
    [Remark]                NTEXT           NULL,
    [WxTransactionId]       NVARCHAR (50)   NULL,
    [WxRefundId]            NVARCHAR (50)   NULL,
    [VisitNo]               NVARCHAR (50)   NULL,
    [Sqno]                  NVARCHAR (50)   NULL,
    [CardName] NVARCHAR(50) NULL, 
    [BookingNo] NVARCHAR(50) NULL, 
    [SvObjectId] NVARCHAR(50) NULL, 
    [InfoSeq] NVARCHAR(50) NULL, 
    CONSTRAINT [PK_ApptOrder] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'挂号预约记录', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'订单类型 0-微信平台预约订单  1-其它平台预约订单 2-微信平台挂号订单', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'OrderType';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'微信号Id', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'OpenId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'卡类型: 0-诊疗卡1-健康卡 2-市民卡 3-未知', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'CardType';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'患者信息卡卡号', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'CardNo';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'医院Id', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'HospitalId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'分园区Id 01番禹 02越秀03海珠', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'BranchNo';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'科室院内编码', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'DeptId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'科室名称', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'DeptName';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'医生院内编码', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'DoctorId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'医生名称', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'DoctorName';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'订单号', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'OrderNo';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'就诊日期', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'RegDate';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'取号成功后，号序', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'RegNo';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'取号成功后，建议就诊开始时间', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'SuggestBeginTime';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'取号成功后，建议就诊结束时间', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'SuggestEndTime';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'取号成功后，就诊地点', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'Location';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'0未锁号 1锁号中 2锁号失败 3已锁号 4取消锁号中 5取消锁号成功 6取消锁号失败 7预约中 8预约成功 9预约失败 10取消预约中 11取消预约成功 12取消预约失败 13支付中 14支付失败 15支付成功 16取号中 17取号失败 18退款中 19退款成功 20退款失败 21取号成功 22微信退号中 23微信退号失败 24微信退号成功 25微信退号退款中 26微信退号退款失败 27微信退号退款成功 28窗口退号成功', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'OrderStatus';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'预约总时段：上午，下午，夜间', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'TimeSlice';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'分时开始时间', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'StartTime';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'分时结束时间', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'EndTime';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'号别', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'RegType';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'支付状态:0-未付款,1-已付款,2-退款中,3-已退款', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'PayState';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'退款原因，1-取号失败而退费，2-支付完成时订单已关闭（支付超时）', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'RefundReason';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'停诊状态：0-正常，1-已停诊', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'HaltState';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'挂号费', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'Fee';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'诊疗费', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'TreatFee';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'其他费用', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'OtherFee';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'总费用', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'SumFee';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'订单创建时间', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'CreateTime';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'锁号成功时间', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'LockTime';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'预约成功时间', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'ApptTime';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'支付成功时间，支付成功后微信返回', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'PayTime';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'取号成功时间', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'FetchTime';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'退款成功时间', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'RefundTime';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'用户取消预约的时间', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'CancelByUserTime';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'医院取消订单时间（停诊）', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'CancelByHospTime';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'系统取消预约的时间(超时或系统出错而导致的取消)', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'CancelBySysTime';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'系统下次检查时间', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'NextCheckTime';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'设置系统下次检查时间时的订单状态', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'OrderStatusOnSetCheck';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'系统检查预留字段', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'CheckReserved';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'备注', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'Remark';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'微信支付交易号，支付成功后微信返回', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'WxTransactionId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'微信支付退款单号，退款成功后微信返回', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'WxRefundId';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'取号成功后，就诊流水号', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'VisitNo';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'分时序号', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'ApptOrder', @level2type = N'COLUMN', @level2name = N'Sqno';


GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'就诊人姓名',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'CardName'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'His订单号',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'BookingNo'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'服务对象，用于获取优惠金额',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'SvObjectId'
GO
EXEC sp_addextendedproperty @name = N'MS_Description',
    @value = N'HIS锁号ID',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'ApptOrder',
    @level2type = N'COLUMN',
    @level2name = N'InfoSeq'